package com.zhi.dao;

import com.zhi.entity.ALeave;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

/**
 * 请假记录对象逻辑dao层,与数据库交互
 */
public interface LeaveRepository extends JpaRepository<ALeave, Integer>{


	@Query("SELECT a.id,a.username,a.password,a.userType FROM User a")
	public List<Object[]> getEntityListWithCodeName();

	/**
	 * 只分页查询请假记录
	 * @param pageable
	 * @return
	 */
	@Query(value = "select id,Student_Id,start_Date,end_Date,reason,opinion,state,apply_Date,audit_Date from aleave",
			countQuery ="select count(*) from aleave",
			nativeQuery = true)
	public Page<ALeave> getEntityListWithCodeName(Pageable pageable);

	/**
	 * 分页查询请假记录，根据学生id和请假状态
	 * @param pageable
	 * @return
	 */
	@Query(value = "select id," +
			"(select CONCAT(c.grade_name,'--',b.student_id,'--',b.name) from student b left join grade c on b.grade_id=c.grade_id where b.student_id=a.student_id) Student_Id," +
			"start_Date,end_Date,reason,opinion," +
			"(select b.code_name from dictionary b where a.state=b.code and code_type = 'leaveState') state," +
			"apply_Date,audit_Date from aleave a " +
			"where student_Id in ?1 and state in ?2 ",
			countQuery ="select count(*) from aleave a where student_Id in ?1 and state in ?2",
			nativeQuery = true)
	public Page<ALeave> getEntityListWithCodeName(Pageable pageable,List<String> studentIds,List<String> stateList);

	/**
	 * 分页查询请假记录，根据请假编号和状态
	 * 此处使用JPA的一对多关联查询
	 * @param pageable
	 * @return
	 */
	@Query(value = "select a.* from aleave a where a.id in ?1 and state in ?2 ",
			countQuery ="select count(*) from aleave a where a.id in ?1 and state in ?2",
			nativeQuery = true)
	public Page<ALeave> getEntityListWithCodeName2(Pageable pageable,List<Integer> aLeaveIds,List<String> stateList);

}
